const { Sequelize } = require("sequelize");
const {
    Student,
    Order,
    MovieTicket,
    Appointment,
} = require("../modal/connect");

const router = require("koa-router")();

// 第一套
router.get("/dashboard", async (ctx) => {
    let students = await Student.findOne({
        attributes: [
            [Sequelize.fn("AVG", Sequelize.col("salary")), "salary"],
            [Sequelize.fn("COUNT", Sequelize.col("id")), "num"],
            [Sequelize.fn("AVG", Sequelize.col("age")), "age"],
            [
                Sequelize.fn(
                    "COUNT",
                    Sequelize.fn("DISTINCT", Sequelize.col("className"))
                ),
                "className",
            ],
        ],
    });
    ctx.body = students;
});
// 查询班级，平均薪资，并且按照班级分组
router.get("/salary", async (ctx) => {
    let salary = await Student.findAll({
        attributes: [
            "className",
            [Sequelize.fn("AVG", Sequelize.col("salary")), "salary"],
        ],
        group: ["className"],
    });
    ctx.body = salary;
});
// 每个区间的个数
// 区间取别名
// 别名分组取个数
router.get("/class", async (ctx) => {
    let salary = await Student.findAll({
        attributes: [
            [
                Sequelize.literal(`
                case
                  when salary < 10000 then "1万以下"
                  when salary between 10000 and 15000 then "1-1.5万"
                  when salary between 15000 and 20000 then "1.5-2万"
                  else "2万以上"
                end          
          `),
                "salary_range",
            ],
            [Sequelize.fn("COUNT", Sequelize.col("id")), "count"],
        ],
        group: ["salary_range"],
    });
    ctx.body = salary;
});

// 第二套
router.get("/orderDashboard", async (ctx) => {
    let dashboard = await Order.findOne({
        attributes: [
            [Sequelize.fn("SUM", Sequelize.col("price")), "total"],
            [Sequelize.fn("COUNT", Sequelize.col("order_id")), "count"],
            // 先获取月份
            // 再去重
            // 再数数目
            [
                Sequelize.fn(
                    "COUNT",
                    Sequelize.fn(
                        "DISTINCT",
                        Sequelize.fn("MONTH", Sequelize.col("order_date"))
                    )
                ),
                "months",
            ],
        ],
    });
    ctx.body = dashboard;
});
// 按照月份分组，求每月的销售额
router.get("/orderMonth", async (ctx) => {
    let orderMonth = await Order.findAll({
        attributes: [
            [Sequelize.fn("SUM", Sequelize.col("price")), "sum"],
            [Sequelize.fn("MONTH", Sequelize.col("order_date")), "month"],
        ],
        group: ["month"],
    });
    ctx.body = orderMonth;
});

// 按照月份分组，求每月的销售额
router.get("/orderCategory", async (ctx) => {
    let orderCategory = await Order.findAll({
        attributes: [
            [Sequelize.col("category"), "name"],
            [Sequelize.fn("SUM", Sequelize.col("price")), "value"],
        ],
        group: ["name"],
    });
    ctx.body = orderCategory;
});

// 第三套
router.get("/ticketDashboard", async (ctx) => {
    let dashboard = await MovieTicket.findOne({
        attributes: [
            [Sequelize.fn("COUNT", Sequelize.col("order_id")), "count"],
            [Sequelize.fn("SUM", Sequelize.col("ticket_price")), "sum"],
        ],
    });
    let movies = await MovieTicket.findAll({
        attributes: ["movie_name"],
        group: ["movie_name", "movie_session"],
    });
    ctx.body = {
        dashboard,
        movies: movies.length,
    };
});
router.get("/ticketTypeCount", async (ctx) => {
    let ticketTypeCount = await MovieTicket.findAll({
        attributes: [
            [Sequelize.col("ticket_type"), "name"],
            [Sequelize.fn("COUNT", Sequelize.col("order_id")), "value"],
        ],
        group: ["name"],
    });

    ctx.body = ticketTypeCount;
});
router.get("/ticketTypeSum", async (ctx) => {
    let ticketTypeSum = await MovieTicket.findAll({
        attributes: [
            "movie_name",
            [Sequelize.fn("SUM", Sequelize.col("ticket_price")), "sum"],
        ],
        group: ["movie_name"],
    });

    ctx.body = ticketTypeSum;
});

// 第四套
router.get("/appointmentDashboard", async (ctx) => {
    let appointmentDashboard = await Appointment.findOne({
        attributes: [
            [Sequelize.fn("COUNT", Sequelize.col("appointment_id")), "count"],
            [
                Sequelize.fn(
                    "COUNT",
                    Sequelize.fn("DISTINCT", Sequelize.col("department"))
                ),
                "department",
            ],
            [
                Sequelize.fn(
                    "COUNT",
                    Sequelize.fn("DISTINCT", Sequelize.col("doctor_name"))
                ),
                "doctors",
            ],
            [Sequelize.fn("AVG", Sequelize.col("fee")), "fee"],
        ],
    });
    ctx.body = appointmentDashboard;
});
router.get("/appointmentDaily", async (ctx) => {
    let appointmentDaily = await Appointment.findAll({
        attributes: [
            [Sequelize.fn("DAY", Sequelize.col("appointment_date")), "day"],
            [Sequelize.fn("SUM", Sequelize.col("fee")), "fee"],
        ],
        group: ["day"],
    });
    ctx.body = appointmentDaily;
});

router.get("/appointmentDepartment", async (ctx) => {
    let appointmentDaily = await Appointment.findAll({
        attributes: [
            [Sequelize.col("department"), "name"],
            [Sequelize.fn("COUNT", Sequelize.col("appointment_id")), "value"],
        ],
        group: ["name"],
    });
    ctx.body = appointmentDaily;
});
module.exports = router;
